﻿Public Class QuanLyNo

    Private row_index As Integer = -1
    Private mano As String = ""
    Private dt As New DataTable

    Private Sub XoaTrang()
        mano = ""
        txtNoiDungNo.Text = String.Empty
        txtSoTien.Text = 0
        txtLaiSuat.Text = 0
        cbThoiHan.Text = String.Empty
        txtSoTienTraTrongThang.Text = 0
    End Sub

    Private Sub GanText(ByVal index As Integer)
        txtNoiDungNo.Text = DataGridView1.Rows(index).Cells(1).Value
        txtSoTien.Text = DataGridView1.Rows(index).Cells(2).Value
        txtLaiSuat.Text = DataGridView1.Rows(index).Cells(3).Value
        cbThoiHan.Text = DataGridView1.Rows(index).Cells(4).Value
        txtSoTienTraTrongThang.Text = DataGridView1.Rows(index).Cells(5).Value
        mano = DataGridView1.Rows(index).Cells(0).Value
    End Sub

    Private Sub QuanLyNo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim str As String = "SELECT MaNo, TenNo, SoTien, LaiSuat, NgayNo, STThang FROM No"

        Try
            dt = ProcFunc.ExecuteFuncSQL(str)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        If dt.Rows.Count > 0 Then
            DataGridView1.DataSource = dt
            GanText(0)
            row_index = 0
        End If

    End Sub

    Private Sub btnSoanLai_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSoanLai.Click
        XoaTrang()
    End Sub


    Private Sub btnThem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThem.Click
        If txtNoiDungNo.Text <> "" And txtSoTien.Text <> "" And txtLaiSuat.Text <> "" And cbThoiHan.Text <> "" And txtSoTienTraTrongThang.Text <> "" Then
            'Tao ma ngan hang
            mano = ProcFunc.ExecuteFuncSSQL("select max(MaNo) from No").ToString()
            If mano = "" Then
                mano = "MN1"
            Else
                mano = "MN" & (mano.Substring(2, mano.Length - 2) + 1)
            End If

            Dim str As String = "insert into No values ('" & mano & "',N'" & txtNoiDungNo.Text & "',N'" & cbThoiHan.Text & "'," & txtSoTien.Value.ToString() &
                                "," & txtLaiSuat.Value.ToString & "," & txtSoTienTraTrongThang.Value.ToString & ")"
            Try
                ProcFunc.ExecuteProcSQL(str)

                'Thêm mot dong vao datagridview khong can load lai data
                Dim row As DataRow
                row = dt.NewRow

                row(0) = mano
                row(1) = txtNoiDungNo.Text
                row(2) = txtSoTien.Text
                row(3) = txtLaiSuat.Value
                row(4) = cbThoiHan.Text
                row(5) = txtSoTienTraTrongThang.Text

                dt.Rows.Add(row)
                DataGridView1.CurrentCell = DataGridView1.Rows(dt.Rows.Count - 1).Cells(1)
                row_index = dt.Rows.Count - 1

                MessageBox.Show("Insert thành công")
            Catch ex As Exception
                MessageBox.Show("Lỗi")
            End Try
            Return

        Else
            MessageBox.Show("Nhập đầy đủ thông tin")
        End If
    End Sub

    Private Sub btnXoa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoa.Click
        If mano = "" Then
            Return
        End If

        Dim res As MsgBoxResult = MsgBox("Bạn có muốn xóa", MsgBoxStyle.YesNo, "Thông báo")
        Select Case res
            Case MsgBoxResult.Yes

                Dim str = "delete from No where MaNo = '" & mano & "'"
                ProcFunc.ExecuteProcSQL(str)

                XoaTrang()
                DataGridView1.Rows.RemoveAt(row_index)

                MessageBox.Show("Dữ liệu đã xóa thành công!")

            Case MsgBoxResult.No
                Return
        End Select
    End Sub

    Private Sub btnSua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSua.Click
        If txtNoiDungNo.Text <> "" And txtSoTien.Text <> "" And txtLaiSuat.Text <> "" And cbThoiHan.Text <> "" And txtSoTienTraTrongThang.Text <> "" Then

            Dim str As String = "update No set TenNo = N'" & txtNoiDungNo.Text & "', NgayNo = N'" & cbThoiHan.Text & "', SoTien =" & txtSoTien.Value.ToString.Trim &
                ", LaiSuat = " & txtLaiSuat.Value.ToString & ", STThang = " & txtSoTienTraTrongThang.Value.ToString &
                " where MaNo = '" & mano & "'"
            Try
                ProcFunc.ExecuteProcSQL(str)

                'Thêm mot dong vao datagridview khong can load lai data
                Dim row As DataRow
                row = dt.Rows(row_index)
                row(0) = mano
                row(1) = txtNoiDungNo.Text
                row(2) = txtSoTien.Text
                row(3) = txtLaiSuat.Value
                row(4) = cbThoiHan.Text
                row(5) = txtSoTienTraTrongThang.Text

                MessageBox.Show("Dữ liệu đã được sửa")
            Catch ex As Exception
                MessageBox.Show("Lỗi")
            End Try
            Return

        Else
            MessageBox.Show("Xin vui lòng điền đủ thông tin")
        End If
    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        If e.RowIndex < 0 Or e.ColumnIndex < 0 Then
            Return
        End If
        row_index = e.RowIndex
        GanText(e.RowIndex)
    End Sub
End Class